// dbRemoteList.js
const path = require('path');
const sqlite3 = require('sqlite3').verbose();

class RemoteListDB {
    constructor(app) {
        try {
            this.app = app;
            this.userDataPath = app.userDataPath;
            this.dbPath = path.join(this.userDataPath, 'remotes.db');
            this.db = new sqlite3.Database(this.dbPath);
            this.init();
            console.log(`## Open RemoteListDB OK [${this.dbPath}]`);
        } catch (error) {
            throw new Error('## Open RemoteListDB Failed, Error: ' + error.message);
        }
    }

    // 初始化sqlite信息
    init() {
        // status标识当前remote的连接状态，取值如下：
        const sqlDatasetsTable = `
        CREATE TABLE IF NOT EXISTS remotes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            host TEXT NOT NULL,
            port INTEGER NOT NULL DEFAULT 22,
            username TEXT NOT NULL,
            password TEXT NOT NULL,
            identity_file TEXT,
            tunnel_src_port INTEGER,
            tunnel_dst_port INTEGER,
            ssh_status INTEGER DEFAULT 0,
            server_status INTEGER DEFAULT 0,
            server_env TEXT DEFAULT ""
        );`;

        this.db.run(sqlDatasetsTable, (err) => {
            if (err) {
                console.error('Create "remotes" Table Failed, Error: ', err);
            }
        });
    }

    // 获取远程连接机器的信息
    async getRemoteList() {
        return new Promise((resolve, reject) => {
            const sqlQuery = `SELECT * FROM remotes`;
            this.db.all(sqlQuery, (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // 获取远程机器的信息
    async getRemoteInfo(remoteId) {
        return new Promise((resolve, reject) => {
            const sqlGetRemoteInfo = `
                SELECT * FROM remotes WHERE id = ?;`;

            this.db.all(sqlGetRemoteInfo, [remoteId], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    if (rows.length > 0) {
                        resolve(rows[0]);
                    } else {
                        reject(`No remote with ${remoteId}`);
                    }
                }
            });
        });
    }

    // 新增远程连接的机器
    async addRemote(remoteInfo) {
        return new Promise(async (resolve, reject) => {
            try {
                let { name, host, port, username, password, identity_file } = remoteInfo;

                // 获取当前最大的src_port
                const maxSrcPortQuery = 'SELECT MAX(tunnel_src_port) AS maxSrcPort FROM remotes;';
                this.db.get(maxSrcPortQuery, (err, result) => {
                    if (err) {
                        console.warn(`execute maxSrcSql Failed, err: `, err);
                        reject(err);
                        return;
                    }

                    // 生成调用的端口
                    let src_port = 3001;
                    if (result.maxSrcPort) {
                        src_port = result.maxSrcPort + 1;
                    }

                    // Insert the new category
                    const sqlAddRemote = `INSERT INTO remotes (name, host, port, username, password, identity_file, tunnel_src_port, tunnel_dst_port) VALUES (?, ?, ?, ?, ?, ?, ?, 3000);`;

                    this.db.run(sqlAddRemote, [name, host, port, username, password, identity_file, src_port], function (err) {
                        if (err) {
                            reject(err);
                        } else {
                            resolve(this.lastID);
                        }
                    });
                });
            } catch (error) {
                reject(error);
            }
        });
    }

    // 编辑远程连接的机器
    async updateRemote(remoteInfo) {
        const params = [];
        let sqlUpdate = 'UPDATE remotes SET';

        let fields = ['name', 'address', 'port', 'username', 'password', 'identity_file', 'tunnel_src_port', 'tunnel_dst_port', 'ssh_status', 'server_status', 'server_env'];
        for (let i = 0; i < fields.length; i++) {
            let field = fields[i];
            if (remoteInfo[field] != null && remoteInfo[field] != undefined) {
                sqlUpdate += ` ${field} = ?,`;
                params.push(remoteInfo[field]);
            }
        }

        // Remove the trailing comma and add the WHERE clause
        sqlUpdate = sqlUpdate.slice(0, -1) + ' WHERE id = ?;';
        params.push(remoteInfo['id']);

        return new Promise((resolve, reject) => {
            this.db.run(sqlUpdate, params, function (err) {
                if (err) {
                    reject({ code: -1, msg: err.message });
                } else {
                    if (this.changes > 0) {
                        resolve({
                            code: 0,
                            msg: `Updated remote with ID ${remoteInfo['id']} OK`
                        });
                    } else {
                        reject({
                            code: -2,
                            msg: `No remote found with ID ${remoteInfo['id']}`
                        });
                    }
                }
            });
        });
    }

    // 删除远程连接的机器
    async deleteRemote(remoteId) {
        return new Promise((resolve, reject) => {
            const sqlDeleteDataset = `DELETE FROM remotes WHERE id = ?;`;
            this.db.run(sqlDeleteDataset, [remoteId], async (err) => {
                if (err) {
                    console.error('Error deleting remote:', err.message);
                    reject({ code: -1, msg: err.message });
                }
                // 确认删除后则返回结果
                resolve({ code: 0, msg: `Remote ${remoteId} Deleted OK` });
            });
        });
    }

    // 关闭RemoteListDB的资源
    async close() {
        return new Promise((resolve, reject) => {
            // 关闭数据库连接
            this.db.close((err) => {
                if (err) {
                    console.error('## Close RemoteListDB Failed, Error: ', err);
                    reject(err);
                } else {
                    console.log('## Close RemoteListDB OK!');
                    resolve();
                }
            });
        });
    }
}

module.exports = { RemoteListDB };
